import pymysql
from faker import Faker
from pymysql.cursors import DictCursor

fake = Faker(locale='zh_CN')
name1 = fake.name()
name2 = fake.name()
print(name1)
print(name2)

db = pymysql.connect(host='127.0.0.1',
                     port=3306,
                     user='root',
                     password='123!@#Qw',
                     database='wangzhe',
                     cursorclass=DictCursor,
                     )

cs = db.cursor()
# sql_insert = f'insert into person  values (default ,"{name}",21,"女","蜀国",6)'
sql_insert = f'insert into person  values (default ,%s,%s,%s,%s,%s)'
sql_select = 'select * from person;'
sql_delete1 = f'delete from person  where name ="{name1}"'
sql_delete2 = f'delete from person  where name ="{name2}"'
try:
    print(cs.executemany(sql_insert, [(name1, 23, '男', '魏国', 2), (name2, 25, '男', '魏国', 2)]))
    db.commit()
    # data = cs.fetchall()
    # for i in data:
    #     print(i)
    print(cs.execute(sql_delete1))
    print(cs.execute(sql_delete2))
except Exception as e:
    print(e)
    print("回滚事务")
    db.rollback()
finally:
    db.commit()
    cs.execute(sql_select)
    data = cs.fetchall()
    for i in data:
        print(i)
    cs.close()
db.close()
